﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;

namespace Cinser.DBUtility.DAL
{

    public class DBHelperOracle
    {
        public OracleConnection conn = null;
        private OracleCommand cmd = null;
        private OracleDataAdapter adapter = null;
        private bool isTrans = false;
        private OracleTransaction trans = null;
        string connStr;
        public DBHelperOracle(string constr)
        {

            connStr = constr;
        }
        /**
         * 获取数据库连接对象
         * */
        public void getConn()
        {
            conn = new OracleConnection(connStr);
        }
        /**
         * 打开数据库连接
         * */
        public void openConn()
        {
            getConn();
            if ((conn != null) && (conn.State == System.Data.ConnectionState.Closed))
            {
                conn.Open();
            }
        }
        /**
         *  关闭数据库连接
         * */
        public void closeConn()
        {
            if ((conn != null) && (conn.State != System.Data.ConnectionState.Closed) && !isTrans)
            {
                conn.Close();
            }
        }
        /**
         * 开始事务
         * */
        public void beginTrans()
        {
            openConn();
            isTrans = true;
            trans = conn.BeginTransaction();
        }
        /**
         * 提交事务
         * */
        public void commitTrans()
        {
            trans.Commit();
            isTrans = false;
            closeConn();
        }
        /**
         * 回滚事务
         * */
        public void rollbackTrans()
        {
            trans.Rollback();
            isTrans = false;
            closeConn();
        }
        /**
         * 执行DML语句
         * */
        public void execSql(string sql)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }

            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            closeConn();
        }
        /**
         * 执行DML语句（可带参数）
         * */
        public int execSql(string sql, List<OracleParameter> para)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }

            cmd.CommandText = sql;
            for (int i = 0; i < para.Count; i++)
            {
                cmd.Parameters.Add(para[i]);
            }
            int result = cmd.ExecuteNonQuery();
            closeConn();
            return result;
        }
        /// <summary>
        /// 执行多条Sql 语句
        /// </summary>
        /// <param name="sqls"></param>
        public void execSqls(string[] sqls)
        {
            openConn();
            cmd = conn.CreateCommand();
            for (int i = 0; i < sqls.Length; i++)
            {
                cmd.CommandText = sqls[i];
                cmd.ExecuteNonQuery();
            }
            closeConn();
        }
        public void execSqls(string[] sqls, List<List<OracleParameter>> parasList)
        {
            openConn();
            for (int i = 0; i < sqls.Length; i++)
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = sqls[i];
                for (int j = 0; j < parasList[i].Count; j++)
                {
                    cmd.Parameters.Add(parasList[i][j]);
                }
                cmd.ExecuteNonQuery();
            }
            closeConn();
        }
        public object execScalar(string sql, List<OracleParameter> para)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }

            cmd.CommandText = sql;
            for (int i = 0; i < para.Count; i++)
            {
                cmd.Parameters.Add(para[i]);
            }
            object result = cmd.ExecuteScalar();
            closeConn();
            return result;
        }
        /**
         * 执行DML语句，并返回单独一列的值
         * */
        public object execScalar(string sql)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
            }
            cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            object returnval = cmd.ExecuteScalar();
            closeConn();
            return returnval;
        }
        /**
         * 执行DML语句，并返回影响行数
         * */
        public int execSqlREF(string sql)
        {
            int J;
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }

            cmd.CommandText = sql;
            J = cmd.ExecuteNonQuery();
            closeConn();
            return J;
        }
        /**
         * 执行DML语句（可带参数）并返回影响行数
         * */
        public int execSqlREF(string sql, OracleParameter[] para)
        {
            int J = 0;
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }
            cmd.CommandText = sql;
            if (para != null)
            {
                for (int i = 0; i < para.Length; i++)
                {
                    cmd.Parameters.Add(para[i]);
                }
            }
            J = cmd.ExecuteNonQuery();
            closeConn();
            return J;
        }
        /**
         * 通过SQL语句返回结果集
         * */
        public System.Data.DataSet getDataSet(string sql)
        {
            DataSet dataSet = new DataSet();
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }
            cmd.CommandText = sql;
            adapter = new OracleDataAdapter(cmd);
            adapter.Fill(dataSet);
            closeConn();
            return dataSet;
        }
        /**
        * 通过SQL语句返回结果集
        * */
        public System.Data.DataSet getDataSet(string sql, OracleParameter[] para)
        {
            DataSet dataSet = new DataSet();
            try
            {
                if (isTrans)
                {
                    cmd = conn.CreateCommand();
                    cmd.Transaction = trans;
                }
                else
                {
                    openConn();
                }
                cmd = getSqlCmd(sql, para);
                adapter = new OracleDataAdapter(cmd);
                adapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return dataSet;
        }
        /**
         * 将参数传入Command对象后返回该Command对象
         **/
        private OracleCommand getSqlCmd(string sql, OracleParameter[] para)
        {
            OracleCommand command = conn.CreateCommand();
            command.CommandText = sql;
            for (int i = 0; i < para.Length; i++)
            {
                command.Parameters.Add(para[i]);
            }
            return command;
        }
    }
}
